package com.zhan.ielts.utils;

import com.zhan.ielts.domain.Topic;
import com.zhan.ielts.domain.excel.TopicPart1;
import com.zhan.ielts.domain.excel.TopicPart23;
import com.zhan.ielts.utils.annotation.ExcelAnnotation;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author futianliang
 * @date 2015年8月4日
 */
public class ExcelUtils {
    /**
     * map 中String是sheet名称，Object为list的value集合
     *
     * @param map
     * @param out
     * @throws Exception
     */
    public static void excelExport(Map<String, Object> map, OutputStream out) throws Exception {
        if (map != null) {
            Workbook workbook = new XSSFWorkbook();
            for (String key : map.keySet()) {
                List<? extends Object> list = (List<? extends Object>) map.get(key);
                if (!CollectionUtils.isEmpty(list)) {
                    Sheet sheet = workbook.createSheet(key);
                    setExcelHeads(list.get(0), sheet);
                    setExcelCotent(list, sheet);
                    sheet.autoSizeColumn(1);
                    //sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));
                }
            }
            try {
                workbook.write(out);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void excelExport(List<? extends Object> list, OutputStream out) throws Exception {
        if (!CollectionUtils.isEmpty(list)) {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("sheet1");
            setExcelHeads(list.get(0), sheet);
            setExcelCotent(list, sheet);
            sheet.autoSizeColumn(1, true);
            try {
                workbook.write(out);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static void setExcelCotent(List<? extends Object> list, Sheet sheet) throws Exception {
        int rowIndex = 1;
        for (Object entity : list) {
            Class cls = entity.getClass();
            Field[] fileds = cls.getDeclaredFields();
            Row row = sheet.createRow(rowIndex);
            List<Field> fieldlist = new ArrayList<Field>();
            if (fileds != null && fileds.length > 0) {
                for (Field f : fileds) {
                    f.setAccessible(true);
                    ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);
                    if (excel.isShow()) {
                        fieldlist.add(f);
                    }
                }
            }
            Collections.sort(fieldlist, new Comparator<Field>() {
                @Override
                public int compare(Field o1, Field o2) {
                    o1.setAccessible(true);
                    ExcelAnnotation excel = o1.getAnnotation(ExcelAnnotation.class);
                    o2.setAccessible(true);
                    ExcelAnnotation excel2 = o2.getAnnotation(ExcelAnnotation.class);
                    return excel.id() > excel2.id() ? 1 : -1;
                }
            });
            for (int i = 0; i < fieldlist.size(); i++) {
                Field f = fieldlist.get(i);
                Cell cell = row.createCell(i);
                // RichTextString text = new XSSFRichTextString((String)
                // f.get(entity));
                String value = (String) f.get(entity);
            /*	if (value != null) {
                    //cell.setCellValue(value.trim().replaceAll("\\s*|\t|\r|\n", ""));
				} else {
					cell.setCellValue(value);
				}*/
                if (value == null) {
                    value = "";
                }
                cell.setCellValue(value);
            }
            rowIndex++;
        }
    }

    private static void setExcelHeads(Object entity, Sheet sheet) {
        List<ExcelAnnotation> excels = new ArrayList<ExcelAnnotation>();
        Class cls = entity.getClass();
        Field[] fileds = cls.getDeclaredFields();
        if (fileds != null && fileds.length > 0) {
            for (Field f : fileds) {
                f.setAccessible(true);
                ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);
                if (excel.isShow()) {
                    excels.add(excel);
                }
            }
        }
        Collections.sort(excels, new Comparator<ExcelAnnotation>() {
            @Override
            public int compare(ExcelAnnotation o1, ExcelAnnotation o2) {
                return o1.id() > o2.id() ? 1 : -1;
            }
        });
        Row rowHeader = sheet.createRow(0);
        XSSFCellStyle style = ((XSSFWorkbook) sheet.getWorkbook()).createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        for (int i = 0; i < excels.size(); i++) {
            Cell cell = rowHeader.createCell(i);
            cell.setCellStyle(style);
            RichTextString text = new XSSFRichTextString(excels.get(i).name());
            cell.setCellValue(text);
            //sheet.setColumnWidth(i, text.getString().getBytes().length * 300);
        }
    }

    public static Workbook getWorkbook(String filePath) {
        try {
            InputStream input = new FileInputStream(filePath); // 建立输入流
            Workbook wb = null;
            // Biff8EncryptionKey.setCurrentUserPassword("123");
            if (filePath.endsWith("xlsx")) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static <T> List<T> excelImport(Class<T> cls, Workbook wb, String sheetName) {
        List<T> list = new ArrayList<T>();
        try {
            Sheet sheet = null;
            if (sheetName != null) {
                sheet = wb.getSheet(sheetName);
            } else {
                sheet = wb.getSheetAt(0);
            }
            int rows = sheet.getLastRowNum();
            // Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器

            for (int i = 0; i <= rows; i++) {
                Row row = sheet.getRow(i);
                T t = null;
                if (i > 0) {
                    t = cls.newInstance();
                }
                if (row != null) {
                    int cols = row.getLastCellNum();
                    for (int j = 0; j < cols; j++) {
                        Cell cell = row.getCell(j);
                        String value = null;
                        Object cellValue = null;
                        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            /* cellValue = cell.getNumericCellValue();*/
                            DecimalFormat df = new DecimalFormat("0");
                            cellValue = df.format(cell.getNumericCellValue());
                        } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            cellValue = cell.getStringCellValue();
                        } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                            cellValue = cell.getBooleanCellValue();
                        } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                            cellValue = cell.getCellFormula();
                        } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            cellValue = "";
                        }
                        if (cellValue != null) {
                            value = String.valueOf(cellValue);
                        }
                        if (i > 0) {
                            setCellValue2T(t, j, value);
                        }
                    }
                }
                if (t != null) {
                    list.add(t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static <T> List<T> excelImport(Class<T> cls, String filePath, String sheetName) {
        return excelImport(cls, getWorkbook(filePath), sheetName);
    }

    public static Map<String, List> excelImport(Map<String, Class> map, String filePath) {
        Map<String, List> returnMap = new HashMap<String, List>();
        for (String key : map.keySet()) {
            returnMap.put(key, excelImport(map.get(key), filePath, key));
        }
        return returnMap;
    }

    private static void setCellValue2T(Object t, int currentColumn, String value) {
        Class<?> cls = t.getClass();
        Field[] fileds = cls.getDeclaredFields();
        try {
            if (fileds != null) {
                for (Field f : fileds) {
                    f.setAccessible(true);
                    ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);
                    int id = excel.id();
                    if (id == currentColumn) {
                        String name = f.getName();
                        Method m = cls.getDeclaredMethod("set" + name.substring(0, 1).toUpperCase() + name.substring(1), String.class);
                        m.setAccessible(true);
                        m.invoke(t, value);
                    }

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 以下方法类为处理Topic的answer解读不正确重写方法类
     * 命名皆为原方法名+ForTopic 表示为Topic专属处理方法  不影响其他类进行调用
     * 所用参数返回值与原方法相同
     * 对于answer的String类型 多个answer数值解读为List<String> 转换为json格式存于anser字段内
     * overrideBy songhaoyu    2018.5.17
     */

    public static Map<String, List> excelImportForTopic(Map<String, Class> map, String filePath) {
        Map<String, List> returnMap = new HashMap<String, List>();
        Workbook workbook = getWorkbook(filePath);
        for (String key : map.keySet()) {
            if ("part1".equals(key)) {
                List<TopicPart1> topicPart1s = excelImportForTopic1(workbook, key);
                returnMap.put(key, topicPart1s);
            }
            if ("part23".equals(key)) {
                List<TopicPart23> topicPart23s = excelImportForTopic23(workbook, key);
                returnMap.put(key, topicPart23s);
            }
        }
        return returnMap;
    }

    public static List<TopicPart1> excelImportForTopic1(Workbook wb, String sheetName) {
        List<TopicPart1> list = new ArrayList<TopicPart1>();
        try {
            Sheet sheet = null;
            if (sheetName != null) {
                sheet = wb.getSheet(sheetName);
            } else {
                sheet = wb.getSheetAt(0);
            }
            //获取最大行下标
            int rowNum = sheet.getLastRowNum();
            Row row;
            Cell cell;
            int cellNum;
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (null == row) {
                    break;
                } else {
                    //获取行长度
                    cellNum = row.getLastCellNum();
                    TopicPart1 topicPart1 = new TopicPart1();
                    //code
                    cell = row.getCell(0);
                    if (null == cell || StringUtils.isEmpty(cell.getStringCellValue())) {
                        break;
                    }
                    String code = cell.getStringCellValue();
                    topicPart1.setCode(code);
                    //title
                    cell = row.getCell(1);
                    String title = cell.getStringCellValue();
                    topicPart1.setTitle(title);
                    //part1
                    cell = row.getCell(2);
                    String part1 = cell.getStringCellValue();
                    topicPart1.setPart1(part1);
                    //flag
                    cell = row.getCell(3);
                    String flag = cell.getStringCellValue();
                    topicPart1.setFlag(flag);
                    //上线录音
                    cell = row.getCell(4);
                    String onLineAudio = cell.getStringCellValue();
                    topicPart1.setOnLineAudio(onLineAudio);
                    //*已有录音
                    //*已有录音为excel表字段，为编辑机经的老师做备忘的，不入库
                    //frequency
                    cell = row.getCell(6);
                    String frequency = cell.getStringCellValue();
                    topicPart1.setFrequency(frequency);
                    //priority
                    cell = row.getCell(7);
                    double priority = cell.getNumericCellValue();
                    topicPart1.setPriority(String.valueOf(priority));
                    //isUpdate
                    cell = row.getCell(8);
                    String isUpdate = cell.getStringCellValue();
                    topicPart1.setIsUpdate(isUpdate);
                    //循环获取answers
                    ArrayList<String> answerList = new ArrayList<>();
                    for (int j = 9; j < cellNum; j++) {
                        cell = row.createCell(j);
                        if (StringUtils.isEmpty(cell.getStringCellValue())) {
                            break;
                        }
                        answerList.add(cell.getStringCellValue());
                    }
                    topicPart1.setAnswer(JsonUtils.toJson(answerList));

                    list.add(topicPart1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<TopicPart23> excelImportForTopic23(Workbook wb, String sheetName) {
        List<TopicPart23> list = new ArrayList<TopicPart23>();
        try {
            Sheet sheet;
            if (sheetName != null) {
                sheet = wb.getSheet(sheetName);
            } else {
                sheet = wb.getSheetAt(1);
            }
            //获取最大行下标
            int rowNum = sheet.getLastRowNum();
            Row row = null;
            Cell cell = null;
            int cellNum;
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (null == row) {
                    break;
                } else {
                    //获取行长度
                    cellNum = row.getLastCellNum();
                    TopicPart23 topicPart23 = new TopicPart23();
                    //code
                    cell = row.getCell(0);
                    if (null == cell) {
                        break;
                    } else {
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        if (StringUtils.isEmpty(cell.getStringCellValue())) {
                            break;
                        }
                    }
                    String code = cell.getStringCellValue();
                    topicPart23.setCode(code);
                    //title
                    cell = row.getCell(1);
                    String title = cell.getStringCellValue();
                    topicPart23.setTitle(title);
                    //part2
                    cell = row.getCell(2);
                    String part2 = cell.getStringCellValue();
                    topicPart23.setPart2(part2);
                    //part3
                    cell = row.getCell(3);
                    String part3 = cell.getStringCellValue();
                    topicPart23.setPart3(part3);
                    //type
                    cell = row.getCell(4);
                    String type = cell.getStringCellValue();
                    topicPart23.setType(type);
                    //上线录音
                    cell = row.getCell(5);
                    String onLineAudio = cell.getStringCellValue();
                    topicPart23.setOnLineAudio(onLineAudio);
                    //*已有录音
                    //*已有录音为excel表字段，为编辑机经的老师做备忘的，不入库

                    //flag
                    cell = row.getCell(7);
                    String flag = cell.getStringCellValue();
                    topicPart23.setFlag(flag);
                    //frequency
                    cell = row.getCell(8);
                    String frequency = cell.getStringCellValue();
                    topicPart23.setFrequency(frequency);
                    //priority
                    cell = row.getCell(9);
                    double priority = cell.getNumericCellValue();
                    topicPart23.setPriority(String.valueOf(priority));
                    //isUpdate
                    cell = row.getCell(10);
                    String isUpdate = cell.getStringCellValue();
                    topicPart23.setIsUpdate(isUpdate);
                    //part2Answer
                    cell = row.getCell(11);
                    if (null == cell) {
                        list.add(topicPart23);
                        continue;
                    }
                    String part2Answer = cell.getStringCellValue();
                    topicPart23.setPart2Answer(part2Answer);
                    //循环获取Part3Answer
                    ArrayList<String> answerList = new ArrayList<>();
                    for (int j = 12; j < cellNum; j++) {
                        cell = row.getCell(j);
                        if (null == cell || StringUtils.isEmpty(cell.getStringCellValue())) {
                            break;
                        }
                        answerList.add(cell.getStringCellValue());
                    }
                    topicPart23.setPart3Answer(JsonUtils.toJson(answerList));
                    list.add(topicPart23);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void main(String[] args) {

    }
}
